﻿Create View [PerformanceMonitoring].[ExpensiveQueries] As
  select top 100 percent
         Db_Name( database_id ) as DatabaseName
        ,Object_Schema_Name( object_id, database_id ) As SchemaName
        ,Object_Name( object_id, database_id ) As TableName
        ,'Create NonClustered Index [Idx_' + Object_Name( object_id, database_id ) + '_'
           + Replace( Replace( Replace( IsNull( equality_columns, '' ), ', ', '_' ), '[', '' ), ']', '' )
           + Case When equality_columns Is Not Null And inequality_columns Is Not Null Then '_' Else '' End
           + Replace( Replace( Replace( IsNull( inequality_columns, '' ), ', ', '_' ), '[', '' ), ']', '' )
           + '] On ' + QuoteName( Object_Schema_Name( object_id, database_id ) ) + '.' + QUOTENAME( Object_Name( object_id, database_id ) ) + ' ' 
           + QuoteName( IsNull( equality_columns + ',' + inequality_columns, IsNull( equality_columns, '' ) + IsNull( inequality_columns, '' ) ), ')' )
           + IsNull( ' Include ' + QuoteName( included_columns, ')' ), '' ) As CreateIndexStatement
        ,last_user_seek As LastUserSeek
        ,user_seeks As UserSeeks
        ,user_scans As UserScans
        ,avg_user_impact As AvgUserImpact
        ,avg_user_impact * ( user_seeks + user_scans ) As TotalEstimatedImpact
    from sys.dm_db_missing_index_groups
   inner join sys.dm_db_missing_index_group_stats on dm_db_missing_index_group_stats.group_handle = dm_db_missing_index_groups.index_group_handle
   inner join sys.dm_db_missing_index_details on dm_db_missing_index_groups.index_handle = dm_db_missing_index_details.index_handle
   order by TotalEstimatedImpact desc